# -*- coding: utf-8 -*-
from flask import Blueprint, request, redirect, url_for
from flask import Blueprint, render_template
from common.models.bpm_data import BpmData
from common.libs.Helper import ops_renderJSON, iPagenation, ops_render
from controllers.index import index_page
import pandas as pd
from application import ExoskeletonEngine as engine
from echarts.base_bar import base_bar
from echarts.base_pie import base_pie
from echarts.reversal_bar import reversal_bar
from echarts.timeline_table import timeline_table
import datetime
from dateutil.relativedelta import relativedelta

bpm_page = Blueprint("bpm_page", __name__)

sql = ''' select * from bpm_data; '''
ordata = pd.read_sql_query(sql, engine)


# 西北公司数据
@bpm_page.route("/api_bpm_time_xb")
def api_bpm_time_xb():
    date = request.args.get("date")
    data = ordata[ordata["date"] == date].loc[:, ["company", "timenum", "date"]]
    data = data["timenum"].groupby(data["company"]).mean().reset_index().round(2).replace(["地产集团西北区域公司","地产集团西北区域山西公司","地产集团西北区域内蒙古公司","地产集团西北区域陕西公司","地产集团西北区域兰州公司","地产集团西北区域乌鲁木齐公司"],["西北区域","山西公司","内蒙公司","陕西公司","兰州公司","乌鲁木齐"])
    data.sort_values(by="timenum", inplace=True, ascending=True)
    x = data["company"].values.tolist()
    y1 = data["timenum"].values.tolist()
    showdata = reversal_bar(x=x, y1=y1, title="审批时长", legend="审批时长（单位：小时）", color='#d7ab82',)
    return showdata.dump_options_with_quotes()

@bpm_page.route("/api_bpm_total_xb")
def api_bpm_total_xb():
    date = request.args.get("date")
    data = ordata[ordata["date"] == date].loc[:, ["company", "totalnum", "date"]]
    data = data["totalnum"].groupby(data["company"]).sum().reset_index().replace(["地产集团西北区域公司","地产集团西北区域山西公司","地产集团西北区域内蒙古公司","地产集团西北区域陕西公司","地产集团西北区域兰州公司","地产集团西北区域乌鲁木齐公司"],["区域","山西","内蒙古","陕西","兰州","乌鲁木齐"])

    x = data["company"].values.tolist()
    y1 = data["totalnum"].values.tolist()
    showdata = base_pie(x=x, y1=y1, title="审批单数(单位：条)")
    return showdata.dump_options_with_quotes()

@bpm_page.route("/api_bpm_mark_xb")
def api_bpm_mark_xb():
    date = request.args.get("date")
    data = ordata[ordata["date"] == date].loc[:, ["company", "totalnum","timenum","date"]]
    data1 = data["timenum"].groupby(data["company"]).mean().reset_index()
    data2 = data["totalnum"].groupby(data["company"]).sum().reset_index()
    # 合并
    data=pd.merge(data1, data2, on='company').replace(["地产集团西北区域公司","地产集团西北区域山西公司","地产集团西北区域内蒙古公司","地产集团西北区域陕西公司","地产集团西北区域兰州公司","地产集团西北区域乌鲁木齐公司"],["西北区域","山西公司","内蒙公司","陕西公司","兰州公司","乌鲁木齐"])
    data["mark"]=data["totalnum"]/data["timenum"]
    data.sort_values(by="mark", inplace=True, ascending=False)
    x = data["company"].values.tolist()
    y1 = data["mark"].round(1).values.tolist()
    showdata = base_bar(x=x, y1=y1, title="审批效率分数", legend="得分", color='#d87c7c',rotate=0)
    return showdata.dump_options_with_quotes()


# 表格区

# 首页（默认为上个月）
@bpm_page.route("/index")
def bpmindex():
    date = datetime.date.today() - relativedelta(months=+1)
    datestr = date.strftime("%Y%m")
    leaderoa = ["xiaoyizhen", "yuxiangchun", "yanhanxiang1",  "xiangshurong"]
    leader_date = BpmData.query.filter(BpmData.oa.in_(leaderoa)).filter(BpmData.date == datestr).all()
    return ops_render("/bpm/xb.html", {"leader_xb": leader_date, "datestr": datestr,"area":"xb"})

# 西北表格
@bpm_page.route("/xb")
def bpmxb():
    date = request.args.get("date")
    leaderoa = ["xiaoyizhen", "yuxiangchun", "yanhanxiang1",  "xiangshurong"]
    leader_date = BpmData.query.filter(BpmData.oa.in_(leaderoa)).filter(BpmData.date == date).all()
    return ops_render("/bpm/xb.html", {"leader_xb": leader_date, "datestr": date,"area":"xb"})

# 山西表格
@bpm_page.route("/sx")
def bpmsx():
    date = request.args.get("date")
    leaderoa = ["gengyi", "zhangchengsheng2",  "xuerui1", "wangxin","dongjunqing","zhaoqian5","liuzhitao","fulisheng","wangjinlong","anpeng","yanpeng3","lihuadong","haoyuan"]
    leader_date = BpmData.query.filter(BpmData.oa.in_(leaderoa)).filter(BpmData.date == date).all()
    return ops_render("/bpm/sx.html", {"leader_xb": leader_date, "datestr": date,"area":"sx"})

# 陕西表格
@bpm_page.route("/sx2")
def bpmsx2():
    date = request.args.get("date")
    leaderoa = ["xuhuqing", "fengbingwu",  "liushuofeng", "yanxinglong","zhaojianhua1","caozheng","lijiangtao1","qianlong","yinguodong","mengyan","wangwenli","wanghui24","wuzaichao"]
    leader_date = BpmData.query.filter(BpmData.oa.in_(leaderoa)).filter(BpmData.date == date).all()
    return ops_render("/bpm/sx2.html", {"leader_xb": leader_date, "datestr": date,"area":"sx2"})

# 内蒙表格
@bpm_page.route("/nm")
def bpmnm():
    date = request.args.get("date")
    leaderoa = ["lingminjun","yuanliang","changlulu","lirong1","cuimengling","tanjian","liuwenliang","gaoguodong1","wuxiaopeng","mengxujun","huangya","kangzilin"]
    leader_date = BpmData.query.filter(BpmData.oa.in_(leaderoa)).filter(BpmData.date == date).all()
    return ops_render("/bpm/nm.html", {"leader_xb": leader_date, "datestr": date,"area":"nm"})

# 乌鲁木齐/兰州表格
@bpm_page.route("/wl")
def bpmwl():
    date = request.args.get("date")
    leaderoaw = ["wangjiangang", "sujing", "niezhenhua", "gaimingwei"]
    leaderoal = ["zhoujianfeng","duanhongtao","zhangqinming","wanglijuan2"]
    leader_datew = BpmData.query.filter(BpmData.oa.in_(leaderoaw)).filter(BpmData.date == date).all()
    leader_datel = BpmData.query.filter(BpmData.oa.in_(leaderoal)).filter(BpmData.date == date).all()
    return ops_render("/bpm/wl.html", {"leader_datew": leader_datew, "leader_datel": leader_datel,"datestr": date,"area":"wl"})



# 图表区
# 西北图表
@bpm_page.route("/api_bpm_leader_xb_time")
def api_bpm_leader_xb_time():
    date = request.args.get("date")
    leaderoa = ["xiaoyizhen", "yuxiangchun", "yanhanxiang1",
              "xiangshurong"
              ]
    data = ordata[(ordata["oa"].isin(leaderoa)) & (ordata["date"] == date)].loc[:, [
                                                                                       "name",
                                                                                       "timenum",
                                                                                       "date",
                                                                                   ]]
    x = data["name"].values.tolist()
    y1 = data["timenum"].values.tolist()
    showdata = base_bar(x=x, y1=y1, title="审批时长", legend="平均审批时间（单位：小时）", color='#d7ab82',rotate=0)
    return showdata.dump_options_with_quotes()


@bpm_page.route("/api_bpm_leader_xb_total")
def api_bpm_leader_xb_total():
    date = request.args.get("date")
    leaderoa = ["xiaoyizhen", "yuxiangchun", "yanhanxiang1",
               "xiangshurong"
              ]

    data = ordata[(ordata["oa"].isin(leaderoa))&(ordata["date"]==date)].loc[:, [
                                                          "name",
                                                          "totalnum",
                                                          "date",
                                                      ]]
    x = data["name"].values.tolist()
    y1 = data["totalnum"].values.tolist()
    showdata = base_bar(x=x, y1=y1, title="审批单数", legend="审批单数（单位：条）", color='#919e8b',rotate=0)
    return showdata.dump_options_with_quotes()

# 山西图表
@bpm_page.route("/api_bpm_leader_sx_time")
def api_bpm_leader_sx_time():
    date = request.args.get("date")
    leaderoa = ["gengyi", "zhangchengsheng2",  "xuerui1", "wangxin","dongjunqing","zhaoqian5","liuzhitao","fulisheng","wangjinlong","anpeng","yanpeng3","lihuadong","haoyuan"]
    data = ordata[(ordata["oa"].isin(leaderoa)) & (ordata["date"] == date)].loc[:, [
                                                                                       "name",
                                                                                       "timenum",
                                                                                       "date",
                                                                                   ]]
    x = data["name"].values.tolist()
    y1 = data["timenum"].values.tolist()
    showdata = base_bar(x=x, y1=y1, title="审批时长", legend="平均审批时间（单位：小时）", color='#d7ab82',rotate=0)
    return showdata.dump_options_with_quotes()


@bpm_page.route("/api_bpm_leader_sx_total")
def api_bpm_leader_sx_total():
    date = request.args.get("date")
    leaderoa = ["gengyi", "zhangchengsheng2", "xuerui1", "wangxin", "dongjunqing", "zhaoqian5", "liuzhitao",
                "fulisheng", "wangjinlong", "anpeng", "yanpeng3", "lihuadong","haoyuan"]

    data = ordata[(ordata["oa"].isin(leaderoa))&(ordata["date"]==date)].loc[:, [
                                                          "name",
                                                          "totalnum",
                                                          "date",
                                                      ]]
    x = data["name"].values.tolist()
    y1 = data["totalnum"].values.tolist()
    showdata = base_bar(x=x, y1=y1, title="审批单数", legend="审批单数（单位：条）", color='#919e8b',rotate=0)
    return showdata.dump_options_with_quotes()


# 陕西图表
@bpm_page.route("/api_bpm_leader_sx2_time")
def api_bpm_leader_sx2_time():
    date = request.args.get("date")
    leaderoa = ["xuhuqing", "fengbingwu", "liushuofeng", "yanxinglong", "zhaojianhua1", "caozheng", "lijiangtao1",
                "qianlong", "yinguodong", "mengyan", "wangwenli", "wanghui24", "wuzaichao"]
    data = ordata[(ordata["oa"].isin(leaderoa)) & (ordata["date"] == date)].loc[:, [
                                                                                       "name",
                                                                                       "timenum",
                                                                                       "date",
                                                                                   ]]
    x = data["name"].values.tolist()
    y1 = data["timenum"].values.tolist()
    showdata = base_bar(x=x, y1=y1, title="审批时长", legend="平均审批时间（单位：小时）", color='#d7ab82',rotate=0)
    return showdata.dump_options_with_quotes()


@bpm_page.route("/api_bpm_leader_sx2_total")
def api_bpm_leader_sx2_total():
    date = request.args.get("date")
    leaderoa = ["xuhuqing", "fengbingwu",  "liushuofeng", "yanxinglong","zhaojianhua1","caozheng","lijiangtao1","qianlong","yinguodong","mengyan","wangwenli","wanghui24","wuzaichao"]

    data = ordata[(ordata["oa"].isin(leaderoa))&(ordata["date"]==date)].loc[:, [
                                                          "name",
                                                          "totalnum",
                                                          "date",
                                                      ]]
    x = data["name"].values.tolist()
    y1 = data["totalnum"].values.tolist()
    showdata = base_bar(x=x, y1=y1, title="审批单数", legend="审批单数（单位：条）", color='#919e8b',rotate=0)
    return showdata.dump_options_with_quotes()

# 内蒙图表
@bpm_page.route("/api_bpm_leader_nm_time")
def api_bpm_leader_nm_time():
    date = request.args.get("date")
    leaderoa = ["lingminjun","yuanliang","changlulu","lirong1","cuimengling","tanjian","liuwenliang","gaoguodong1","wuxiaopeng","mengxujun","huangya","kangzilin"]
    data = ordata[(ordata["oa"].isin(leaderoa)) & (ordata["date"] == date)].loc[:, [
                                                                                       "name",
                                                                                       "timenum",
                                                                                       "date",
                                                                                   ]]
    x = data["name"].values.tolist()
    y1 = data["timenum"].values.tolist()
    showdata = base_bar(x=x, y1=y1, title="审批时长", legend="平均审批时间（单位：小时）", color='#d7ab82',rotate=0)
    return showdata.dump_options_with_quotes()


@bpm_page.route("/api_bpm_leader_nm_total")
def api_bpm_leader_nm_total():
    date = request.args.get("date")
    leaderoa = ["lingminjun","yuanliang","changlulu","lirong1","cuimengling","tanjian","liuwenliang","gaoguodong1","wuxiaopeng","mengxujun","huangya","kangzilin"]

    data = ordata[(ordata["oa"].isin(leaderoa))&(ordata["date"]==date)].loc[:, [
                                                          "name",
                                                          "totalnum",
                                                          "date",
                                                      ]]
    x = data["name"].values.tolist()
    y1 = data["totalnum"].values.tolist()
    showdata = base_bar(x=x, y1=y1, title="审批单数", legend="审批单数（单位：条）", color='#919e8b',rotate=0)
    return showdata.dump_options_with_quotes()


# 乌鲁木齐图表
@bpm_page.route("/api_bpm_leader_wlmq_time")
def api_bpm_leader_wlmq_time():
    date = request.args.get("date")
    leaderoa = ["wangjiangang", "sujing", "niezhenhua", "gaimingwei"]
    data = ordata[(ordata["oa"].isin(leaderoa)) & (ordata["date"] == date)].loc[:, [
                                                                                       "name",
                                                                                       "timenum",
                                                                                       "date",
                                                                                   ]]
    x = data["name"].values.tolist()
    y1 = data["timenum"].values.tolist()
    showdata = base_bar(x=x, y1=y1, title="审批时长", legend="平均审批时间（单位：小时）", color='#d7ab82',rotate=0)
    return showdata.dump_options_with_quotes()


@bpm_page.route("/api_bpm_leader_wlmq_total")
def api_bpm_leader_wlmq_total():
    date = request.args.get("date")
    leaderoa = ["wangjiangang","sujing","niezhenhua","gaimingwei"]

    data = ordata[(ordata["oa"].isin(leaderoa))&(ordata["date"]==date)].loc[:, [
                                                          "name",
                                                          "totalnum",
                                                          "date",
                                                      ]]
    x = data["name"].values.tolist()
    y1 = data["totalnum"].values.tolist()
    showdata = base_bar(x=x, y1=y1, title="审批单数", legend="审批单数（单位：条）", color='#919e8b',rotate=0)
    return showdata.dump_options_with_quotes()


# 兰州图表
@bpm_page.route("/api_bpm_leader_lz_time")
def api_bpm_leader_lz_time():
    date = request.args.get("date")
    leaderoa = ["zhoujianfeng","duanhongtao","zhangqinming","wanglijuan2"]
    data = ordata[(ordata["oa"].isin(leaderoa)) & (ordata["date"] == date)].loc[:, [
                                                                                       "name",
                                                                                       "timenum",
                                                                                       "date",
                                                                                   ]]
    x = data["name"].values.tolist()
    y1 = data["timenum"].values.tolist()
    showdata = base_bar(x=x, y1=y1, title="审批时长", legend="平均审批时间（单位：小时）", color='#d7ab82',rotate=0)
    return showdata.dump_options_with_quotes()


@bpm_page.route("/api_bpm_leader_lz_total")
def api_bpm_leader_lz_total():
    date = request.args.get("date")
    leaderoa = ["zhoujianfeng","duanhongtao","zhangqinming","wanglijuan2"]

    data = ordata[(ordata["oa"].isin(leaderoa))&(ordata["date"]==date)].loc[:, [
                                                          "name",
                                                          "totalnum",
                                                          "date",
                                                      ]]
    x = data["name"].values.tolist()
    y1 = data["totalnum"].values.tolist()
    showdata = base_bar(x=x, y1=y1, title="审批单数", legend="审批单数（单位：条）", color='#919e8b',rotate=0)
    return showdata.dump_options_with_quotes()
# @bpm_page.route("/index")
# def bpmindex():
#     num = [9,10,11]
#     dategroup = []
#     leaderoa = ["xiaoyizhen","yuxiangchun","yanhanxiang1","sujing"]
#     for item in num:
#         date = datetime.date.today() - relativedelta(months=+item)
#         dateint = int(date.strftime("%Y%m"))
#         dategroup.append(dateint)
#
#     # bpmData = BpmData.query.filter(BpmData.oa.in_(leaderoa))
#     print(dategroup)
#     leader_xb = []
#     for item in dategroup:
#         leader_date = BpmData.query.filter(BpmData.oa.in_(leaderoa)).filter(BpmData.date == item).all()
#         leader_xb.append(leader_date)
#
#     return ops_render("/bpm/bpmindex.html",
#                       {"leader_xb": leader_xb,"dategroup":dategroup})
#
#
#
# @bpm_page.route("/api_bpm_leader_xb_time")
# def bpm_leader_xb_time():
#     leader = ["肖一珍", "于向春", "燕瀚翔",
#               "苏景",
#
#               ]
#     data = ordata[ordata["name"].isin(leader)].loc[:, [
#                                                           "name",
#                                                           "timenum",
#                                                           "date",
#                                                       ]]
#     data['month'] = pd.DatetimeIndex(data['date']).month
#
#     # data['year'] = data['date'].astype("str").apply(lambda x: x[:4])
#     # data['month'] = data['date'].astype("str").apply(lambda x: x[4:])
#
#     showdata = timeline_bar(data=data, range=data['date'], rangetype="date", keyx="name", keyy="timenum", title="",
#                             legend="平均审批时间（单位：小时）", color='#d87c7c')
#
#     return showdata.dump_options_with_quotes()
#
#
# @bpm_page.route("/api_bpm_leader_xb_total")
# def bpm_leader_xb_total():
#     leader = ["肖一珍", "于向春", "燕瀚翔",
#               "苏景",
#
#               ]
#     data = ordata[ordata["name"].isin(leader)].loc[:, [
#                                                           "name",
#                                                           "totalnum",
#                                                           "date",
#                                                       ]]
#     data['month'] = pd.DatetimeIndex(data['date']).month
#
#     # data['year'] = data['date'].astype("str").apply(lambda x: x[:4])
#     # data['month'] = data['date'].astype("str").apply(lambda x: x[4:])
#
#     showdata = timeline_bar(data=data, range=data['date'], rangetype="date", keyx="name", keyy="totalnum", title="",
#                             legend="审批单据数（单位：条）", color='#919e8b')
#
#     return showdata.dump_options_with_quotes()
#
#
# @bpm_page.route("/api_bpm_leader_xb_table")
# def api_bpm_leader_xb_table():
#     leader = ["肖一珍", "于向春", "燕瀚翔",
#               "苏景",
#               ]
#     headers = ["name", "oa", "company", "timenum", "totalnum"]
#
#     data = ordata[ordata["name"].isin(leader)].loc[:, [
#                                                           "name",
#                                                           "totalnum",
#                                                           "date",
#                                                       ]]
#
#     data['month'] = pd.DatetimeIndex(data['date']).month
#
#     showdata = timeline_table(data=data, range=data['date'], rangetype="date", title="",
#                               legend="审批单据数（单位：条）", color='#919e8b')
#
#     return showdata.dump_options_with_quotes()
